/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Documents;

import com.sun.org.apache.xerces.internal.impl.dv.util.Base64;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.sql.*;
import java.util.Iterator;
import java.util.Vector;
import javax.imageio.ImageIO;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(DocumentsBean db) throws SQLException {
        int i = 0, k;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into documents values(documentid.nextval,?,sysdate)");
            pre.setString(1, db.getDocumentName());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(document_id) from documents");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static DocumentsBean viewSpecific(String cname, String val) throws SQLException {
        DocumentsBean db = new DocumentsBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from documents where " + cname + "=?");
            pre.setString(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                db.setDocumentId(rs.getInt(1));
                db.setDocumentName(rs.getString(2));
                db.setEntryDate(rs.getDate(3));
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return db;

    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from documents order by document_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                DocumentsBean db = new DocumentsBean();
                db.setDocumentId(rs.getInt(1));
                db.setDocumentName(rs.getString(2));
                db.setEntryDate(rs.getDate(3));
                list.add(db);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;

    }

    public static int edit(DocumentsBean db) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update documents set document_name=?, entrydate=sysdate where document_id=?");
            pre.setString(1, db.getDocumentName());
            pre.setInt(2, db.getDocumentId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from documents where document_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int addStuDocs(Vector list) throws SQLException {
        int i = 0 ;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into STUDENT_DOCUMENT values(STUDDOCID.nextval,?,?,?,sysdate,?,?)");
            Iterator it = list.iterator();
            while (it.hasNext()) {
                StudentDocumentBean sdb = (StudentDocumentBean)it.next();
                pre.setInt(1, sdb.getDocumentId());
                pre.setString(2, sdb.getDocumentNo());
                pre.setInt(3, sdb.getAdmissionId());
                pre.setString(4, sdb.getEntryEmail());
                pre.setString(5, sdb.getDocumentName());
                pre.addBatch();
            }
            int k[] = pre.executeBatch();
            if(k.length!=0){
            i=1;
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    
    public static Vector viewSpecific(String cname, int id) throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from STUDENT_DOCUMENT where ADMISSION_ID=?");
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                StudentDocumentBean sdb = new StudentDocumentBean();
                sdb.setStudDocId(rs.getInt(1));
                sdb.setDocumentId(rs.getInt(2));
                sdb.setDocumentNo(rs.getString(3));
                sdb.setAdmissionId(rs.getInt(4));
                sdb.setEntryDate(rs.getDate(5));
                sdb.setEntryEmail(rs.getString(6));
                sdb.setDocumentName(rs.getString(7));
                list.add(sdb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;

    }
    public static String getPhoto(String name)
    {
        String byt="";
        try
        {
            String path="/usr/RITDocuments";
            File fi=new File(path);
            
            File f1=new File(fi.getAbsolutePath()+File.separator+name);
            BufferedImage bi=ImageIO.read(f1);
            ByteArrayOutputStream bout=new ByteArrayOutputStream();
            ImageIO.write(bi,"jpg",bout);
            byte bb[]=bout.toByteArray();
            String byt1=Base64.encode(bout.toByteArray()); 
            byt="data:image/jpg;base64,"+byt1;
        }
        catch(Exception e)
        {
            return byt;
        }
        return byt;
    }
    public static String getAttach(String fileName){
        String byt="";
        try{
        }catch(Exception e){
        }finally{
            
        }return byt;
    }
    public static Vector viewSpecificStuAllDocu(int id) throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from STUDENT_DOCUMENT sd left join DOCUMENTS d on sd.DOCUMENT_ID=d.DOCUMENT_ID where ADMISSION_ID=?");
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                StudentDocumentBean sdb = new StudentDocumentBean();
                DocumentsBean db=new DocumentsBean();
                sdb.setStudDocId(rs.getInt(1));
                sdb.setDocumentId(rs.getInt(2));
                sdb.setDocumentNo(rs.getString(3));
                sdb.setAdmissionId(rs.getInt(4));
                sdb.setEntryDate(rs.getDate(5));
                sdb.setEntryEmail(rs.getString(6));
                sdb.setDocumentName(rs.getString(7));
                db.setDocumentId(rs.getInt(8));
                db.setDocumentName(rs.getString(9));
                db.setEntryDate(rs.getDate(10));
                sdb.setDocumentsBean(db);
                list.add(sdb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;

    }
    
      public static Vector viewDocument(String documents) throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from documents  where upper(DOCUMENT_NAME) like upper(?)");
            pre.setString(1, documents+"%");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                DocumentsBean db = new DocumentsBean();
                db.setDocumentId(rs.getInt(1));
                db.setDocumentName(rs.getString(2));
                db.setEntryDate(rs.getDate(3));
                list.add(db);
            }
        } catch (Exception e) {
            System.out.println(e);
            
        } finally {
            con.close();
        }
        return list;

    }
}
